<?php
namespace app\models;

use Yii;
use yii\db\Query;
//use yii\db\CDbCriteria;
/**
 * 
 *

 */
class BaseGoodTypes extends \yii\db\ActiveRecord {
    
     /**
     * @inheritdoc
     */
    public static function tableName() {
        return '{{%base_browse}}';
    }

    /**
     * @inheritdoc
     */
    public function rules() {
        return [
            [['user_id', 'sp_id', 'browse_time'], 'required'],
            [['user_id', 'sp_id', 'browse_time'], 'integer']
        ];
    }

    /**
     * @inheritdoc
     */
    public function attributeLabels() {
        return [
            'id' => 'ID',
            'user_id' => '用户id',
            'sp_id' => '用户id',
            'browse_time' => '浏览时间',
            'status' => '状态',
        ];
    }
    
     /**
     * @inheritdoc
     */
    public static function countBaseGoodQgHy($type,$id) {
    	
        if($id == ''){
        	if($type == 'qg'){
	             $sql = 'select sp_id,count(sp_id) as qgnum,max(cgjg) as maxcgjs,min(cgjg) as mincgjg from dh_base_goods_qg where status=1 group by sp_id';
	             $basegoodsnum = BaseGoodTypes::findBySql($sql)->asArray()->all();
	        }elseif ($type == 'hy') {
	             $sql = 'select sp_id,count(sp_id) as hynum,min(hsdj) as minhsdj,max(hsdj) as maxhsdj from dh_base_goods_hy where status=1 group by sp_id';
	             $basegoodsnum = BaseGoodTypes::findBySql($sql)->asArray()->all();
//				 print_r($basegoodsnum);die();
	        }
        }else{
        	if($type == 'qg'){
	             $sql = 'select sp_id,count(sp_id),max(cgjg) from dh_base_goods_qg where sp_id='.$id.' and status=1';
	             $basegoodsnum = BaseGoodTypes::findBySql($sql)->asArray()->all();
	        }elseif ($type == 'hy') {
	             $sql = 'select sp_id,count(sp_id),min(hsdj) from dh_base_goods_hy where sp_id='.$id.' and status=1';
	             $basegoodsnum = BaseGoodTypes::findBySql($sql)->asArray()->all();
	        }
        }
        
        
        return $basegoodsnum;
    }
    
    
    
     public static function listBaseType($type,$tag=false) {

            if($type == 'one'){
                $basetype = (new Query())->select("*")->from("dh_base_type")->where(['is_deleted'=>0,'category'=>$tag])->all();
            }else if($type == 'all'){
                $base = (new Query())->select("category")->from("dh_base_type")->where(['is_deleted'=>0,'id'=>$tag])->one();
                $basetype = (new Query())->select("*")->from("dh_base_type")->where(['is_deleted'=>0,'category'=>$base['category']])->all();

            }else{
                $basetype = '';
            }
        return $basetype;
    }
    
      public static function listBaseTypes() {

                $sql = 'select * from dh_base_type where is_deleted=0 and category=0';
                $basetype = BaseGoodTypes::findBySql($sql)->asArray()->all();

        return $basetype;
    }
    
     public static function listBaseBrand() {
           
            $sql = 'select * from dh_base_brand where is_deleted=0 limit 10';
            $basebrand = BaseGoodTypes::findBySql($sql)->asArray()->all();

        return $basebrand;
    }
    
    public static function upBasegoods($id) {
           
            $sql = 'select id from dh_base_type where is_deleted=0 and category='.$id;
            $basegoods = BaseGoodTypes::findBySql($sql)->asArray()->all();
            
//            print_r($tag);die();;
        return $basegoods;
    }
    
	//  商品列表
	public static function selBasegoods($array,$condition,$tag,$pages,$page1) {
            
            $pages=(int)$pages;
            $basegoods = (new Query())->select('a.*,b.img_url')->from("dh_base_goods as a") ->leftJoin("dh_base_img as b","a.img_id=b.id")->Where($array)->andWhere('1=1'.$condition)->orderBy($tag)->limit($page1)->offset($pages)->all();
//		print_r($basegoods);die();
            return $basegoods;
        }
        
        
	// 商品分页
        public static function listBaseGoods($array,$condition) {
		
            $basegoods = (new Query())->select('*')->from("dh_base_goods as a") ->Where($array)->andWhere('1=1'.$condition)->count();

            return $basegoods;
        }
	
	public static function selectBaseGoodsId($id,$type=false) {
		
            if(!$type){
                    $sql = 'select a.*,b.name as names,b.id as ids,c.img_url from dh_base_goods a left join dh_base_type b on a.type_id=b.id left join dh_base_img as c on a.img_id=c.id where a.is_deleted=0 and a.id='.$id;
                    $basegoods = BaseGoodTypes::findBySql($sql)->asArray()->one();
            }else{
                    $sql = 'select type_id  from dh_base_goods  where is_deleted=0 and id='.$id;
                    $base = BaseGoodTypes::findBySql($sql)->asArray()->one();
                if(is_numeric($type)){
                    $sql = 'select category from dh_base_type  where is_deleted=0 and id='.$base['type_id'];
                    $basetype = BaseGoodTypes::findBySql($sql)->asArray()->one();
                    $sqls = 'select name,id from dh_base_type  where is_deleted=0 and category='.$basetype['category'].' limit 10';
                    $basegoods = BaseGoodTypes::findBySql($sqls)->asArray()->all();
//				print_r($basegoods);die();
                }else{
                    $sqls = 'select name,id from dh_base_goods  where is_deleted=0 and type_id='.$base['type_id'].' limit 6';
                    $basegoods = BaseGoodTypes::findBySql($sqls)->asArray()->all();
                }
            }
//		print_r($basegoods);die();
        return $basegoods;
			
    }
	
	public static function HotBaseGoodsHy() {

            $sqls = 'select * from dh_base_goods where is_deleted=0 order by created_at DESC limit 3';
            $basegoods = BaseGoodTypes::findBySql($sqls)->asArray()->all();

        return $basegoods;
			
    }
    
        public static function BrowseGoods($user_id) {
            
            $sql = 'select b.*,c.img_url from dh_base_browse as a  left join dh_base_goods as b on a.sp_id=b.id left join dh_base_img as c on b.img_id=c.id where a.user_id='.$user_id.' order by a.browse_time DESC limit 1,5';
            $basegoods = BaseGoodTypes::findBySql($sql)->asArray()->all();
//            $arr = '';
//            foreach ($basegoods as $value) {
//               $arr .=$value['sp_id'].',';  
//            }
//            $arr = substr($arr,0,-1);
//            $sqls = 'select a.*,b.img_url from dh_base_goods as a left join dh_base_img as b on a.img_id=b.id where a.id in ('.$arr.') order by b';
//            $array= BaseGoodTypes::findBySql($sqls)->asArray()->all();
//             print_r($basegoods);die();
             return $basegoods;
         } 
         
         public static function SelType($array,$type,$tag,$pages,$page1) {
            
             if($type == 'hy'){
                 $goodshy = (new Query())->select("a.*,b.*")->from("dh_base_goods_hy as a")->leftJoin("dh_sys_users as b",'a.qy_id = b.id')->where($array)->orderBy($tag)->limit($page1)->offset($pages)->all();
                  
             }else if($type == 'qg'){
                  $goodshy = (new Query())->select("a.*,b.*")->from("dh_base_goods_qg as a")->leftJoin("dh_sys_users as b",'a.uid = b.id')->where($array)->orderBy($tag)->limit($page1)->offset($pages)->all();
//                 print_r($goodshy);die();
             }
             
             return $goodshy;
         } 
         
         public static function SelTypeCount($array,$type) {
            
             if($type == 'hy'){
                $goodshy = (new Query())->select("a.*")->from("dh_base_goods_hy as a")->where($array)->count();
             }else if($type == 'qg'){
                $goodshy = (new Query())->select("a.*")->from("dh_base_goods_qg as a")->where($array)->count();
             }
//             print_r($array);die();
             return $goodshy;
         }
         
         public static function listmin($id,$type){
             if($type == 'hy'){
                $sqls = 'select * from dh_base_goods_hy where status=1 and flag=1 and sp_id='.$id.' order by hsdj ASC limit 5';
                $goods = BaseGoodTypes::findBySql($sqls)->asArray()->all();
             }else if($type == 'qg'){
                $sqls = 'select * from dh_base_goods_qg where status=1 and flag=1 and sp_id='.$id.' order by cgjg DESC limit 5';
                $goods = BaseGoodTypes::findBySql($sqls)->asArray()->all();
             }
             return $goods;
         }
         
         public static function GoodZy($id){
             $goodszy = (new Query())->select("a.*,b.*")->from("dh_base_goods_hy as a")->leftJoin("dh_sys_users as b",'a.qy_id = b.id')->where("a.sp_id = $id and b.ours =1 and a.status = 1 and a.flag = 1")->orderBy("a.fbsj DESC")->limit(1)->offset(0)->all();
             return $goodszy;
         }
         
         public static function ListGoods($id){
             $str = BaseGoodTypes::getTypeChild($id);
             $sql = "select a.*,MAX(b.hsdj) as maxhy,COUNT(b.sp_id) as sumhy,
                        MAX(c.cgjg) as maxqg ,COUNT(c.sp_id) as sumqg
                        from dh_base_goods as a
                        LEFT JOIN (select * from dh_base_goods_hy where status=1 and flag =1 ) as b on a.id = b.sp_id 
                        LEFT JOIN (select * from dh_base_goods_qg where status =1 and flag =1) as c on a.id = b.sp_id 
                        where a.status = 1 and in(".$str.") GROUP BY a.id";
         }
         
         //取二级分类  返回string
         public static function getTypeChild($id){
             
            $sql = 'select id from dh_base_type where is_deleted=0 and category='.$id;
            $basegoods = BaseGoodTypes::findBySql($sql)->asArray()->all();
            $str = '';
            if(is_array($basegoods)){
                foreach ($basegoods as $value) {
                    $str .= $value['id'].',';  
                }
                 $str = substr($str,0,-1);
            }
            return $str ; 
         }
         
         public static function InsertBrower($uid,$id){
             
            $have = (new Query())->select("*")->from("dh_base_browse")->where(['user_id'=>$uid,'sp_id'=>$id])->count();
//            print_r($have);die();
            $type = FALSE;
            if($have == 0){
               $countbrowse = (new Query())->select("*")->from("dh_base_browse")->where(['user_id'=>$uid])->count();
               if($countbrowse<10){
                    $type = true;
               }else{
                  $countbrowse = (new Query())->select("*")->from("dh_base_browse")->orderBy("browse_time ASC")->limit(1)->offset(0)->one();
                  $res = BaseGoodTypes::findOne(['id'=>$countbrowse['id']]);
                  $res->user_id = $uid;
                  $res->sp_id = $id;
                  $res->browse_time = time();
                  $res->save();
                  $type = FALSE;
               }

            }

            return $type;
           
        }
        
        public static function countOrder(){
            $basetime = strtotime("-3 month");
            $order = (new Query())->select("count(quantity) as quantity,avg(price) as avgprice,b.goodsId")->from("dh_order as a")->leftJoin("dh_order_detail as b","a.orderId=b.orderId")->where("a.status=4 and a.finish_time>=".$basetime)->groupBy("b.goodsId")->all();
//            print_r($order);die();
            return $order;
        }
        
        public static function countOrderId($id){
            $basetime = strtotime("-3 month");
            $order = (new Query())->select("count(quantity) as quantity,avg(price) as avgprice,b.goodsId")->from("dh_order as a")->leftJoin("dh_order_detail as b","a.orderId=b.orderId")->where("a.status=4 and a.finish_time>=".$basetime." and b.goodsId=".$id)->all();
//            print_r($order[0]['avgprice']);die();
            return $order;
        }

}
?>